## 6.5. Sequences and Auto-Incrementing

The best kind of primary key is one that has absolutely no meaning in the database except to act as a primary key. The best way to achieve this is to make a numeric primary key that increments every time you insert a new row. Looking at the `cities` table shown earlier, the first city you insert would have an `id` of 1, the second 2, the third 3, and so on. In order to successfully manage this sequencing of a primary key, you need some way to guarantee that a number can be read and incremented by one and only one client at a time. Under transactional databases, you could create a table called `sequence` that has a number representing the next `id`. When you need to insert a new row, you would read that table and insert a new number one more than the one you read. You must be assured that no one else will read from that table before you insert a new value, however, in order for that scheme to work. Otherwise, two clients could read the same value and attempt to use it as a primary key value in the same table.

Neither MySQL nor mSQL support transactions, so the previously identified mechanism cannot be used for generating unique ID numbers. The MySQL command `LOCK TABLE` is cumbersome for this task. However, both engines support their own variant of a concept called a sequence, which enables you to generate unique ID numbers without worrying about those transactional issues.

### 6.5.1. MySQL Sequences

When you create a table in MySQL, you can specify at most one column as being `AUTO_INCREMENT`. When you do this, you can automatically have this column insert the highest current value for that column + 1 when you insert a row and specify `NULL` or for that row's value. The `AUTO_INCREMENT` row must be indexed. The following command creates the cities table with the `id` field being `AUTO_INCREMENT`:

>     CREATE TABLE cities (id      INT  NOT NULL PRIMARY KEY AUTO_INCREMENT,
>                          name    VARCHAR(100),
>                          pop     MEDIUMINT,
>                          founded DATE)

The first time you insert a row, the `id` field for your first row will be 1 so long as you use `NULL` or for that field in the `INSERT` statement. For example, this command takes advantage of the `AUTO_INCREMENT` feature:

>     INSERT INTO cities (id, name, pop)
>     VALUES (NULL, 'Houston', 3000000)

If no other values are in that table when you issue this command, MySQL will set this field to 1, not `NULL` (remember, it cannot be `NULL`). If other values are present in the table, the value inserted will be one greater than the largest current value for `id`.

Another way to implement sequences is by referring to the value returned by the `LAST_INSERT_ID` function:

>     UPDATE table SET id=LAST_INSERT_ID (id+1);

### 6.5.2. mSQL Sequences

Each mSQL table can have at most one sequence associated with it. The following syntax creates a sequence on a table:

>     CREATE SEQUENCE ON table_name [VALUE start STEP incr]

The start value is the number to start with. The `incr` value is the amount to increment on each access. By default, a sequence starts with 1 and increments 1 at a time. For example:

>     CREATE SEQUENCE ON mytable VALUE 100 STEP 5

This command creates a sequence on the `mytable` table whose first value will be 100 and will increase by 5 each time some accesses the sequence. The second value under this scheme would therefore be 105.

In order to access a sequence, you need to select a special column called `_seq` from the table:

>     SELECT _seq FROM table_name

This will both return to you the next value in the sequence and increment it.

Copyright © 2001 O'Reilly & Associates. All rights reserved.